import os
import pandas as pd
import numpy as np

pd.set_option('display.unicode.east_asian_width', True)


def get_filenames(path):
    filenames = []
    for file in os.listdir(path):
        if file.endswith('.docx'):  # 如果文件以'.txt'结尾
            filenames.append(file)
    return filenames

base_path = r"D:\100-Project\2024-2025-1学期安排\武昌首义学院\29101302_《通信数据分析技术实训（企业）》\实训报告"
excel_file = base_path + r'\数据分析实训报告提交记录.xlsx'
docx_dir = base_path + r"\提交"

df = pd.read_excel(excel_file, header=0)
df.fillna('N', inplace=True)
df = df.astype(str)
df = df.set_index('学号')

filenames = get_filenames(docx_dir)

for filename in filenames:
    if len(filename.split('-')) < 3:
        print(filename)
        exit(0)
    title = filename.split('-')[0].split('》')[1]
    no = filename.split('-')[1]
    name = filename.split('-')[2].split('.')[0]
    try:
        df.loc[no, title] = 'Y'
    except KeyError:
        pass

df.sort_values(by='学号', inplace=True)
print(df)

df.to_excel(excel_file, index=True)